﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;

namespace PwManager
{
    public class SQLiteHelper
    {
        private static string _CONNECTIONSTRING = "Data Source=sqlite\\PWManager.db";

        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            using (var connection = new SQLiteConnection(_CONNECTIONSTRING))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        affectedRows = command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                }
            }
            return affectedRows;
        }

        public static SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {
            using (var connection = new SQLiteConnection(_CONNECTIONSTRING))
            {
                using (var command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    connection.Open();
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
        }

        public static DataTable ExecuteDataTable(string sql, params SQLiteParameter[] parameters)
        {
            var data = new DataTable();
            using (var connection = new SQLiteConnection(_CONNECTIONSTRING))
            {
                using (var command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    new SQLiteDataAdapter(command).Fill(data);
                }
            }
            return data;
        }

        public static Object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
        {
            using (var connection = new SQLiteConnection(_CONNECTIONSTRING))
            {
                using (var command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    connection.Open();
                    return command.ExecuteScalar();
                }
            }
        }


        public static IEnumerable<T> Query<T>(string sql, params SQLiteParameter[] parameters)
        {
            using (var connection = new SQLiteConnection(_CONNECTIONSTRING))
            {
                using (var command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    connection.Open();
                    using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {

                        var typeT = typeof(T);
                        var instance = (T)Activator.CreateInstance(typeof(T));

                        var properties = typeT.GetProperties();

                        while (reader.Read())
                        {
                            for (int i = 0; i < properties.Count(); i++)
                            {
                                typeT.GetProperty(properties[i].Name).SetValue(instance, reader[properties[i].Name], null);
                            }
                            yield return instance;
                        }
                    }
                }
            }
        }
    }
}